package com.kobe.tools.sqlparser;

import lombok.Data;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ：caopengfei
 * @date ：2020/11/9
 */
@Data
public class SqlParserUtil2 {

    public SqlParserUtil2() {

    }

    public  static Map<String,ColumnEntity> createOldTable(String sql)
            throws JSQLParserException {
        Map<String,ColumnEntity> commentsMap = new HashMap<>();
        List<String> comments = getComment(sql);
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        CreateTable createTable = (CreateTable) parserManager.parse(new StringReader(sql));
        List<ColumnDefinition> columnDefinitions = createTable.getColumnDefinitions();
        for(int i = 0 ; i < columnDefinitions.size(); i++ ){
            ColumnEntity columnEntity = createColumnEntity(columnDefinitions.get(i));
            columnEntity.setComment(comments.get(i));
            commentsMap.put(columnEntity.getName(),columnEntity);
        }
        return commentsMap;
    }


    private static List<ColumnEntity>  getNewTable(String sql) throws JSQLParserException {
        List<ColumnEntity> columnEntities = new ArrayList<>();
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        CreateTable createTable = (CreateTable) parserManager.parse(new StringReader(sql));
        List<ColumnDefinition> columnDefinitions = createTable.getColumnDefinitions();
        for(int i = 0 ; i < columnDefinitions.size(); i++ ){
            ColumnEntity columnEntity = createColumnEntity(columnDefinitions.get(i));
            if(columnDefinitions.get(i).getColDataType() != null){
                columnEntity.setDataType(columnDefinitions.get(i).getColDataType().getDataType().toUpperCase());
                List<String> dataTypeLength = columnDefinitions.get(i).getColDataType().getArgumentsStringList();
                if(dataTypeLength != null && dataTypeLength.size()>0 ){
                    columnEntity.setTypeLength(dataTypeLength.get(0));
                }
            }
            columnEntities.add(columnEntity);
        }
        return columnEntities;
    }


    private static  ColumnEntity createColumnEntity(ColumnDefinition columnDefinition ){
        ColumnEntity columnEntity = new ColumnEntity();
        columnEntity.setName(columnDefinition.getColumnName());
        if(columnDefinition .getColumnSpecs() != null && columnDefinition.getColumnSpecs().size() > 1){
            columnEntity.setIsMustNeed("1");
        }
        return columnEntity;
    }

    private void setDataType(){



    }


    public static void main(String[] args) {
        String oldSql  = "create table ZY_BRSYK(\n" +
                "    syxh          ut_syxh    identity\tnot null , --首页序号\n" +
                "    patid         ut_syxh             not null, --病人标识\n" +
                "    blh           ut_blh              not null, --住院号\n" +
                "    mzh\t\t  ut_blh\t\t  null,\t--门诊号\n" +
                "    hzxm          ut_mc32                 null, --姓名\n" +
                "    py            ut_py                   null, --拼音\n" +
                "    wb            ut_py                   null, --五笔\n" +
                "    sfzh          ut_sfzh                 null, --身份证号\n" +
                "    sex           ut_sex                  null, --性别\n" +
                "    birth         ut_rq16                  null, --生日\n" +
                "    brzt          ut_bz               not null, --病人状态(\t0：入院登记)1：病区分床2：病区出院3：病人出院4：取消结算5：进入ICU6：进入产房7：转科状态8：数据转出9：作废记录\n" +
                "    ycqbz\t  \t  ut_bz\t\t      not null,\t--预出区标志 0正常, 1预出区\n" +
                "    ksdm          ut_ksdm                 null, --科室代码\n" +
                "    bqdm          ut_ksdm                 null, --病区代码\n" +
                "    ysdm          ut_czyh                 null, --医生代码\n" +
                "    cwdm          ut_cwdm                 null, --床位\n" +
                "    hldm\t  \t  ut_xmdm\t\t  \t\t  null, --护理代码\n" +
                "    ssdm\t  \t  ut_xmdm\t\t  \t\t  null, --膳食代码\n" +
                "    czyh          ut_czyh                 null, --操作员\n" +
                "    lrrq          ut_rq16                 null, --录入日期\n" +
                "    ryrq          ut_rq16                 null, --入院日期\n" +
                "    rqrq          ut_rq16                 null, --入区日期\n" +
                "    cyrq          ut_rq16                 null, --出院日期\n" +
                "    cqrq          ut_rq16                 null, --出区日期\n" +
                "    wzjb          ut_dm2                  null, --危重级别\n" +
                "    zddm          ut_zddm                 null, --诊断代码\n" +
                "    yjdx          ut_money            not null, --押金停药线\n" +
                "    yjbj          ut_money            not null, --押金报警线\n" +
                "    ryfs          ut_dm2                  null, --入院方式\n" +
                "    cyfs          ut_dm2                  null, --出院方式\n" +
                "    gdxmrq        ut_rq8                  null, --固定项目执行日期\n" +
                "    jgbz          ut_bz               not null, --急观标志(0：住院1：在观2：出观)\n" +
                "    yebz          ut_bz               not null, --婴儿标志\n" +
                "    ybdm          ut_ybdm             not null, --医保代码\n" +
                "    deje          ut_money     \t      not null, --医保定额\n" +
                "    brlx          ut_dm2                  null, --病人类型\n" +
                "    pzh           ut_pzh                  null, --凭证号\n" +
                "    cardno        ut_cardno               null, --主要卡号\n" +
                "    cardtype      ut_dm2                  null, --卡类型\n" +
                "    sbkh          ut_cardno               null, --社保卡号\n" +
                "    qtkh          ut_cardno               null, --其它卡号\n" +
                "    dwbm          ut_ybdwdm               null, --医保单位编码\n" +
                "    zhbz          ut_mc32                 null, --账户标志\n" +
                "    lxr           ut_name                 null, --联系人\n" +
                "    lxrgx         ut_dm2                  null, --联系人关系\n" +
                "    lxrdh         ut_mc16                 null, --联系人电话\n" +
                "    lxrdz  \t  \t  ut_mc64  \t\t  \t\t  null,\t--联系人地址\n" +
                "    zycs          int        default 0    null, --住院次数\n" +
                "    zhye          ut_money            not null, --账户余额\n" +
                "    ybljje        ut_money            not null, --医保累计金额\n" +
                "    tcljje\t  \t  ut_money\t      \t  not null,\t--统筹累计金额\n" +
                "    centerid      ut_lsh                  null,\t--医保中心编号(外地医保)\n" +
                "    memo          ut_memo                 null, --备注\n" +
                "    dbr   \t  \t  ut_mc16 \t\t  \t\t  null, --担保人\n" +
                "    gmxx \t  \t  varchar(100) \t\t  \t  null,\t--病人过敏信息\n" +
                "    qfsx \t  \t  ut_money\t      \t  not null,\t--欠费上限\n" +
                "    cyzddm        ut_zddm                 null, --出院诊断代码\n" +
                "    cyzdmc        ut_mc64                 null,\t--出院诊断名称\n" +
                "    ssdm1         ut_zddm                 null, --手术代码1\n" +
                "    ssdm2         ut_zddm                 null, --手术代码2\n" +
                "    bagxbz        ut_bz \t          \t  null, --病案更新标志\n" +
                "    qjcybz \t  \t  ut_bz \t\t  \t\t  null,\t--病人请假出院标志\n" +
                "    yezcbz  \t  ut_bz \t\t\t\t  null,\t--婴儿转床标志 (0 一起转 1 不一起转)\n" +
                "    brnlsm   \t  ut_name \t\t\t\t  null,\t--医生站使用（年龄的文字说明, 医院自己输入）\n" +
                "\tblgdbz \t\t  ut_bz \t\t\t      null, --病历归档标志\n" +
                "\tbrnldw \t   ut_name \t\t   null,\t--病人年龄单位(YY_NLDWK.name)\n" +
                "\tbrnldw1\t   ut_name \t\t   null,\t--病人年龄单位1(YY_NLDWK.name)\n" +
                "\tyexh \t\tut_syxh  \t\tnull,\t\t\t-- 增加婴儿序号\n" +
                "\tblgdsj \t\t  ut_rq16\t\t\t\t  null,\t--归档时间\n" +
                "\tgbbz \tut_bz \t\t\t\tnull,\t--干保标志\n" +
                "\tmzzdys \tut_czyh \t\t\tnull,\t--门诊诊断医生\n" +
                "\thlzdm   ut_dm4 \t\t\t\tnull,   --护理组代码\n" +
                "\tylzdm   ut_dm4 \t\t\t\tnull,   --医疗组代码\n" +
                "\tsfbrbz \tut_bz \t\t\t\tnull,\t--随访病人标志\n" +
                "\tpzh2 \tut_pzh\t\t\t\tnull,\t--第二凭证号\n" +
                "\tmzzdys2\tut_czyh\t\t\t\tnull,\t--门诊诊断医生2\n" +
                "\ttz      ut_mc16   \t\t\tnull,   --体重\n" +
                "\trywzjb      ut_dm2  \t\t\tnull,   --入院危重级别\n" +
                "\tdbzzddm \tut_zddm  \t\tnull,  \t--单病种诊断代码\n" +
                "\tcyzddybz \tut_bz\t\t\tnull, \t--打印标志 0 未打印  1已经打印\n" +
                "\tcyzddyrq \tut_rq16 \t\tnull, \t--打印时间 \n" +
                "\tcmpdybz \tut_bz\t\t\tnull,\t--触摸屏打印标志\n" +
                "\ttsbz \t\tut_bz  \t\t\tnull,   --推算标志(1年龄为推算, 0实际年龄)\n" +
                "\tislcljbz \tut_bz  \t\t\tnull,  --临床路径标志（1：临床路径属性病人, 非1, 走正常流程的病人）\n" +
                "\tzdqrbz \t\tut_bz  \t\t\tnull,  --诊断确认标志（0或者null:没有医生确认过  1：诊断已经确认）\n" +
                "\tewtwgz \t\tvarchar(10) \t\tnull,  --额外体温规则\n" +
                "\tzhyy \t\tvarchar(1000) \t\tnull,  --召回原因\n" +
                "\tewgzszsj \tdatetime \t\tnull,  --额外规则设置时间\n" +
                "\twdhm \t\tut_xh9 \t\t\tnull,  --腕带号码\n" +
                "\tcqzhshbz \tut_bz \t\t\tnull,  --出区召回审核标志(0未审1已审)\n" +
                "\tylxm   \t\tut_dm2,   \t\t       --入院登记时候选择的大病项目代码\n" +
                "\tblfcbz \t\tut_bz  \t\t\tnull,  --病历封存标志\n" +
                "\tblfcsj \t\tut_rq16  \t\tnull,  --病历封存时间\n" +
                "\tgxrq ut_rq16  null,  --修改日期    --xxl 2012-05-23 需求127347\n" +
                "\tjcdjxh ut_xh12 null, --家床登记序号GH_JZBRDJK.xh   2012-06-05 yfq 需求号：128793 家床系统住院系统改进\n" +
                "\t    constraint PK_ZY_BRSYK primary key (syxh)\n" +
                ")";


        String newSql = "CREATE TABLE ZY_BRSYK(\n" +
                "\tyljgdm varchar(20) NOT NULL,\n" +
                "\tsyxh numeric(9, 0) NOT NULL,\n" +
                "\tpatid numeric(9, 0) NOT NULL,\n" +
                "\tblh varchar(24) NOT NULL,\n" +
                "\tmzh varchar(24) NULL,\n" +
                "\thzxm varchar(64) NULL,\n" +
                "\tpy char(8) NULL,\n" +
                "\twb char(8) NULL,\n" +
                "\tsfzh varchar(20) NULL,\n" +
                "\tsex char(4) NULL,\n" +
                "\tbirth datetime NULL,\n" +
                "\tbrzt smallint NOT NULL,\n" +
                "\tycqbz smallint NOT NULL,\n" +
                "\tksdm char(4) NULL,\n" +
                "\tbqdm char(4) NULL,\n" +
                "\tysdm varchar(12) NULL,\n" +
                "\tcwdm varchar(12) NULL,\n" +
                "\thldm varchar(12) NULL,\n" +
                "\tssdm varchar(12) NULL,\n" +
                "\tczyh varchar(12) NULL,\n" +
                "\tlrrq datetime NULL,\n" +
                "\tryrq datetime NULL,\n" +
                "\trqrq datetime NULL,\n" +
                "\tcyrq datetime NULL,\n" +
                "\tcqrq datetime NULL,\n" +
                "\twzjb char(2) NULL,\n" +
                "\tzddm varchar(20) NULL,\n" +
                "\tyjdx money NOT NULL,\n" +
                "\tyjbj money NOT NULL,\n" +
                "\tryfs char(2) NULL,\n" +
                "\tcyfs char(2) NULL,\n" +
                "\tgdxmrq datetime NULL,\n" +
                "\tjgbz smallint NOT NULL,\n" +
                "\tyebz smallint NOT NULL,\n" +
                "\tybdm char(4) NOT NULL,\n" +
                "\tdeje money NOT NULL,\n" +
                "\tbrlx char(2) NULL,\n" +
                "\tpzh varchar(32) NULL,\n" +
                "\tcardno varchar(32) NULL,\n" +
                "\tcardtype char(2) NULL,\n" +
                "\tsbkh varchar(32) NULL,\n" +
                "\tqtkh varchar(32) NULL,\n" +
                "\tdwbm varchar(12) NULL,\n" +
                "\tzhbz varchar(32) NULL,\n" +
                "\tlxr varchar(12) NULL,\n" +
                "\tlxrgx char(2) NULL,\n" +
                "\tlxrdh varchar(16) NULL,\n" +
                "\tzycs int NULL,\n" +
                "\tzhye money NOT NULL,\n" +
                "\tybljje money NOT NULL,\n" +
                "\ttcljje money NOT NULL,\n" +
                "\tmemo varchar(100) NULL,\n" +
                "\tdbr varchar(16) NULL,\n" +
                "\tdbje money NULL,\n" +
                "\tgmxx varchar(100) NULL,\n" +
                "\tlxrdz varchar(64) NULL,\n" +
                "\tcenterid varchar(36) NULL,\n" +
                "\tssdm1 varchar(20) NULL,\n" +
                "\tssdm2 varchar(20) NULL,\n" +
                "\tcyzddm varchar(20) NULL,\n" +
                "\tcyzdmc varchar(64) NULL,\n" +
                "\tbagxbz smallint NULL,\n" +
                "\tbagxsj datetime NULL,\n" +
                "\tqjcybz smallint NULL,\n" +
                "\tyezcbz smallint NULL,\n" +
                "\tbrnlsm varchar(12) NULL,\n" +
                "\tblgdbz smallint NULL,\n" +
                "\tgbbz smallint NULL,\n" +
                "\tblgdsj datetime NULL,\n" +
                "\tbrnldw varchar(12) NULL,\n" +
                "\tbrnldw1 varchar(12) NULL,\n" +
                "\tmzzdys varchar(12) NULL,\n" +
                "\thlzdm char(4) NULL,\n" +
                "\tylzdm char(4) NULL,\n" +
                "\tsfbrbz smallint NULL,\n" +
                "\tpzh2 varchar(32) NULL,\n" +
                "\thlyz varchar(64) NULL,\n" +
                "\tssyz varchar(64) NULL,\n" +
                "\tmzzdys2 varchar(12) NULL,\n" +
                "\tybcylx varchar(3) NULL,\n" +
                "\ttz varchar(16) NULL,\n" +
                "\trywzjb char(2) NULL,\n" +
                "\tcyzddybz smallint NULL,\n" +
                "\tcyzddyrq datetime NULL,\n" +
                "\tdbzzddm varchar(20) NULL,\n" +
                "\tiszdbr smallint NULL,\n" +
                "\ttsbz smallint NULL,\n" +
                "\tzdqrbz smallint NULL,\n" +
                "\tislcljbz smallint NULL,\n" +
                "\tzzid varchar(36) NULL,\n" +
                "\tdahid varchar(36) NULL,\n" +
                "\tzcsq varchar(255) NULL,\n" +
                "\tzcbz smallint NULL,\n" +
                "\tzhyy varchar(1000) NULL,\n" +
                "\tckrid char(6) NULL,\n" +
                "\tylxm char(2) NULL,\n" +
                "\tbcdyfs smallint NULL,\n" +
                "\tblfcbz smallint NULL,\n" +
                "\tblfcsj datetime NULL,\n" +
                "\tcqzhshbz smallint NULL,\n" +
                "\txnhbh varchar(20) NULL,\n" +
                "\tbirthtime datetime NULL,\n" +
                "\tjcdjxh numeric(12, 0) NULL,\n" +
                "\tzrhsdm varchar(32) NULL,\n" +
                "\ttclbz smallint NULL,\n" +
                "\tsfdh varchar(32) NULL,\n" +
                "\tyzzxlock smallint NULL,\n" +
                "\tgzbz smallint NULL,\n" +
                "\tjsr varchar(32) NULL,\n" +
                "\twcjzbz smallint NULL,\n" +
                "\tljbqdm varchar(12) NULL,\n" +
                "\tzyryms char(2) NULL,\n" +
                "\txlbrryms char(2) NULL,\n" +
                "\tsjysdm varchar(12) NULL,\n" +
                "\tyhdm char(4) NULL,\n" +
                "\tecyhbz smallint NULL,\n" +
                "\tisNew bit NULL,\n" +
                "\tgxrqtimestr varchar(50) NULL,\n" +
                "\ttimetempstr varbinary(8) NULL,\n" +
                "\tcreatetime datetime NULL,\n" +
                "\ttimetemp timestamp NULL,\n" +
                "\tgxrq datetime NOT NULL,\n" +
                "\tsys_id varchar(50) NOT NULL,\n" +
                "\tlsnid bigint NULL,\n" +
                "\tisdelete varchar(8) NULL,\n" +
                "\ttimetempstr_cdc varbinary(10) NULL,\n" +
                "\tredeemId varchar(2) NULL\n" +
                ") ";

        try {
            Map<String,ColumnEntity> oldTable = createOldTable(oldSql);
            List<ColumnEntity> newTable = getNewTable(newSql);
            for(ColumnEntity columnEntity : newTable){
                if(oldTable.containsKey(columnEntity.getName())){
                    columnEntity.setComment(oldTable.get(columnEntity.getName()).getComment());
                }
            }
            System.out.println(newTable);
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }

    }


    private static List<String> getComment(String sql){
        List<String> commtens = new ArrayList<>();
        String [] clomunRow = sql.split("\n");
        for(int i = 1; i< clomunRow.length;i++) {
            if(clomunRow[i].contains("--")){
                String[] array = clomunRow[i].split("--");
                String comment = array[1];
                commtens.add(comment);
            }else{
                commtens.add("");
            }
        }
        return commtens;
    }
}
